"""时序数据库教学演示页面

Streamlit应用页面，展示TimescaleDB与PostgreSQL的性能对比。
"""

import datetime
import time

import pandas as pd
import plotly.graph_objects as go
import streamlit as st
from plotly.subplots import make_subplots
from sqlalchemy import text

from ..config.settings import get_database_config
from ..database.connection import get_db_session
from ..repositories.hydrometric_repository import HydrometricRepository
from ..utils.logger import get_logger

# 获取日志记录器
logger = get_logger(__name__)


def get_database_stats():
    """获取数据库统计信息"""
    try:
        with next(get_db_session()) as session:
            # 获取表记录数
            timeseries_count = session.execute(text("SELECT COUNT(*) FROM hydrometric_data_timeseries")).scalar()
            regular_count = session.execute(text("SELECT COUNT(*) FROM hydrometric_data_regular")).scalar()

            # 获取测站数量
            station_count = session.execute(
                text("SELECT COUNT(DISTINCT station_id) FROM hydrometric_data_timeseries")).scalar()

            # 获取时间范围
            time_range = session.execute(text("""
                SELECT MIN(timestamp), MAX(timestamp) 
                FROM hydrometric_data_timeseries
            """)).fetchone()

            return {
                'timeseries_count': timeseries_count,
                'regular_count': regular_count,
                'station_count': station_count,
                'time_range': time_range
            }
    except Exception as e:
        st.error(f"获取数据库统计信息失败: {e}")
        return None


def get_available_stations(limit=100):
    """获取可用的测站列表"""
    return ['ST000001', 'ST000002', 'ST000003', 'ST000004', 'ST000005', 'ST000006', 'ST000007', 'ST000008', 'ST000009',
            'ST000010']


def get_database_time_range():
    """获取数据库中的时间范围"""
    try:
        with next(get_db_session()) as session:
            repository = HydrometricRepository(session)
            min_time, max_time = repository.get_date_range('timeseries')
            if min_time and max_time:
                return min_time, max_time
            else:
                st.error("数据库中没有找到时间数据")
                return None, None
    except Exception as e:
        st.error(f"获取时间范围失败: {e}")
        return None, None


def run_single_station_test(station_id, start_time, end_time):
    """运行单测站测试"""
    try:
        with next(get_db_session()) as session:
            repository = HydrometricRepository(session)

            # 测试时序表查询
            ts_start = time.time()
            timeseries_data = repository.query_by_station_and_time(
                station_id, start_time, end_time, 'timeseries'
            )
            ts_time = time.time() - ts_start

            # 测试普通表查询
            reg_start = time.time()
            regular_data = repository.query_by_station_and_time(
                station_id, start_time, end_time, 'regular'
            )
            reg_time = time.time() - reg_start

            return {
                'station_id': station_id,
                'time_range': (start_time, end_time),
                'timeseries_data': timeseries_data,
                'regular_data': regular_data,
                'timeseries_time': ts_time,
                'regular_time': reg_time,
                'performance_ratio': reg_time / ts_time if ts_time > 0 else 0
            }
    except Exception as e:
        st.error(f"单测站测试失败: {e}")
        return None


def run_multi_station_test(station_ids, start_time, end_time):
    """运行多测站聚合测试"""
    try:
        with next(get_db_session()) as session:
            repository = HydrometricRepository(session)

            # 测试时序表聚合查询
            ts_start = time.time()
            timeseries_agg = repository.aggregate_by_stations_and_time(
                station_ids, start_time, end_time, 'timeseries'
            )
            ts_time = time.time() - ts_start

            # 测试普通表聚合查询
            reg_start = time.time()
            regular_agg = repository.aggregate_by_stations_and_time(
                station_ids, start_time, end_time, 'regular'
            )
            reg_time = time.time() - reg_start

            return {
                'station_ids': station_ids,
                'station_count': len(station_ids),
                'time_range': (start_time, end_time),
                'timeseries_agg': timeseries_agg,
                'regular_agg': regular_agg,
                'timeseries_time': ts_time,
                'regular_time': reg_time,
                'performance_ratio': reg_time / ts_time if ts_time > 0 else 0
            }
    except Exception as e:
        st.error(f"多测站测试失败: {e}")
        return None


def create_timeseries_chart(data, title, station_id):
    """创建时序数据图表"""
    if not data:
        return None

    df = pd.DataFrame(data)
    if df.empty:
        return None

    fig = go.Figure()

    # 添加水位数据
    if 'water_level' in df.columns:
        fig.add_trace(go.Scatter(
            x=df['timestamp'],
            y=df['water_level'],
            mode='lines+markers',
            name='水位 (m)',
            line=dict(color='#1f77b4', width=2),
            marker=dict(size=4)
        ))

    # 添加流量数据
    if 'flow_rate' in df.columns:
        fig.add_trace(go.Scatter(
            x=df['timestamp'],
            y=df['flow_rate'],
            mode='lines+markers',
            name='流量 (m³/s)',
            line=dict(color='#ff7f0e', width=2),
            marker=dict(size=4),
            yaxis='y2'
        ))

    fig.update_layout(
        title=dict(
            text=f"{title} - 测站 {station_id}",
            font=dict(size=20, color='#2c3e50'),
            x=0.5
        ),
        xaxis_title="时间",
        yaxis_title="水位 (m)",
        yaxis2=dict(
            title="流量 (m³/s)",
            overlaying='y',
            side='right'
        ),
        hovermode='x unified',
        template='plotly_white',
        height=500,
        showlegend=True,
        legend=dict(
            orientation="h",
            yanchor="bottom",
            y=1.02,
            xanchor="right",
            x=1
        )
    )

    return fig


def create_performance_comparison_chart(ts_time, reg_time, test_type):
    """创建性能对比图表"""
    fig = go.Figure()

    categories = ['TimescaleDB\n(时序表)', 'PostgreSQL\n(普通表)']
    times = [ts_time, reg_time]
    colors = ['#4facfe', '#43e97b']

    fig.add_trace(go.Bar(
        x=categories,
        y=times,
        marker_color=colors,
        text=[f'{t:.4f}s' for t in times],
        textposition='auto',
        name='执行时间'
    ))

    fig.update_layout(
        title=dict(
            text=f"{test_type} - 性能对比",
            font=dict(size=18, color='#2c3e50'),
            x=0.5
        ),
        yaxis_title="执行时间 (秒)",
        template='plotly_white',
        height=400,
        showlegend=False
    )

    # 添加性能提升标注
    if ts_time > 0:
        improvement = ((reg_time - ts_time) / ts_time) * 100
        fig.add_annotation(
            x=0.5,
            y=max(times) * 0.8,
            text=f"TimescaleDB 性能提升: {improvement:.1f}%",
            showarrow=True,
            arrowhead=2,
            arrowcolor="#e74c3c",
            bgcolor="#fff",
            bordercolor="#e74c3c",
            borderwidth=2
        )

    return fig


def create_multi_station_chart(agg_data, title):
    """创建多测站聚合数据图表"""
    if not agg_data:
        return None

    df = pd.DataFrame(agg_data)
    if df.empty:
        return None

    fig = make_subplots(
        rows=2, cols=2,
        subplot_titles=('平均流量', '最大流量', '最小流量', '数据点数'),
        specs=[[{"secondary_y": False}, {"secondary_y": False}],
               [{"secondary_y": False}, {"secondary_y": False}]]
    )

    # 平均流量
    if 'avg_flow' in df.columns:
        fig.add_trace(
            go.Scatter(x=df['station_id'], y=df['avg_flow'],
                       mode='markers+lines', name='平均流量',
                       marker=dict(color='#1f77b4', size=8)),
            row=1, col=1
        )

    # 最大流量
    if 'max_flow' in df.columns:
        fig.add_trace(
            go.Scatter(x=df['station_id'], y=df['max_flow'],
                       mode='markers+lines', name='最大流量',
                       marker=dict(color='#ff7f0e', size=8)),
            row=1, col=2
        )

    # 最小流量
    if 'min_flow' in df.columns:
        fig.add_trace(
            go.Scatter(x=df['station_id'], y=df['min_flow'],
                       mode='markers+lines', name='最小流量',
                       marker=dict(color='#2ca02c', size=8)),
            row=2, col=1
        )

    # 数据点数
    if 'count' in df.columns:
        fig.add_trace(
            go.Scatter(x=df['station_id'], y=df['count'],
                       mode='markers+lines', name='数据点数',
                       marker=dict(color='#d62728', size=8)),
            row=2, col=2
        )

    fig.update_layout(
        title=dict(
            text=title,
            font=dict(size=20, color='#2c3e50'),
            x=0.5
        ),
        height=600,
        showlegend=False,
        template='plotly_white'
    )

    return fig


def display_database_overview():
    """显示数据库概览"""
    st.markdown('<h2 class="section-title">📊 数据库概览</h2>', unsafe_allow_html=True)
    with st.expander("📖 课程内容说明", expanded=False):
        # 从docs文件夹加载课程内容
        import os
        project_root = os.path.dirname(os.path.dirname(os.path.dirname(os.path.dirname(__file__))))
        course_content_path = os.path.join(project_root, 'docs/db', 'database-course-content.md')

        try:
            with open(course_content_path, 'r', encoding='utf-8') as f:
                course_content = f.read()
            st.markdown(course_content)
        except FileNotFoundError:
            st.error(f"无法找到课程内容文件: {course_content_path}")
        except Exception as e:
            st.error(f"加载课程内容时出错: {str(e)}")

    stats = get_database_stats()
    if stats:
        col1, col2, col3, col4 = st.columns(4)

        with col1:
            st.markdown(f"""
            <div class="metric-container">
                <h3>⚡ 时序表记录</h3>
                <h2>{stats['timeseries_count']:,}</h2>
                <p>TimescaleDB优化</p>
            </div>
            """, unsafe_allow_html=True)

        with col2:
            st.markdown(f"""
            <div class="metric-container">
                <h3>🗄️ 普通表记录</h3>
                <h2>{stats['regular_count']:,}</h2>
                <p>PostgreSQL标准表</p>
            </div>
            """, unsafe_allow_html=True)

        with col3:
            st.markdown(f"""
            <div class="metric-container">
                <h3>📍 测站数量</h3>
                <h2>{stats['station_count']:,}</h2>
                <p>水文监测站点</p>
            </div>
            """, unsafe_allow_html=True)

        with col4:
            if stats['time_range'] and stats['time_range'][0] and stats['time_range'][1]:
                days = (stats['time_range'][1] - stats['time_range'][0]).days
                st.markdown(f"""
                <div class="metric-container">
                    <h3>📅 数据跨度</h3>
                    <h2>{days} 天</h2>
                    <p>{stats['time_range'][0].strftime('%Y-%m-%d')} 至<br>{stats['time_range'][1].strftime('%Y-%m-%d')}</p>
                </div>
                """, unsafe_allow_html=True)
            else:
                st.markdown("""
                <div class="metric-container">
                    <h3>📅 数据跨度</h3>
                    <h2>无数据</h2>
                    <p>暂无时间信息</p>
                </div>
                """, unsafe_allow_html=True)

        # 数据库连接信息
        db_config = get_database_config()
        st.info(f"🔗 数据库连接: {db_config['host']}:{db_config['port']}/{db_config['database']}")
    else:
        st.error("无法连接到数据库或获取统计信息")


def display_single_station_test():
    """显示单测站测试界面"""
    st.markdown('<h2 class="section-title">🎯 单测站时序数据测试</h2>', unsafe_allow_html=True)

    col1, col2 = st.columns([1, 2])

    with col1:
        st.markdown(
            """
            ### 测试参数\n
            取前10000条径流进行测试。远程演示效果会受网络性能影响波动
            """
        )

        # 获取可用测站
        available_stations = get_available_stations(50)
        if not available_stations:
            st.error("无法获取测站列表")
            return

        station_id = st.selectbox(
            "选择测站",
            available_stations,
            help="选择要测试的水文测站"
        )

        # 获取数据库时间范围
        min_time, max_time = get_database_time_range()
        if not min_time or not max_time:
            st.error("无法获取数据库时间范围")
            return

        st.info(f"数据库时间范围: {min_time.strftime('%Y-%m-%d %H:%M')} 至 {max_time.strftime('%Y-%m-%d %H:%M')}")

        col_time1, col_time2 = st.columns(2)
        with col_time1:
            # 默认开始时间为最近7天前
            default_start_time = max_time.date() - datetime.timedelta(days=7)
            # 确保不早于数据库最小时间
            if default_start_time < min_time.date():
                default_start_time = min_time.date()
            start_time = st.date_input(
                "开始时间",
                value=default_start_time,
                min_value=min_time.date(),
                max_value=max_time.date(),
                help="选择查询开始时间"
            )
        with col_time2:
            end_time = st.date_input(
                "结束时间",
                value=max_time.date(),
                min_value=min_time.date(),
                max_value=max_time.date(),
                help="选择查询结束时间"
            )

        # 验证时间范围
        if start_time >= end_time:
            st.error("开始时间必须早于结束时间")
            return

        if st.button("🚀 开始测试", type="primary", use_container_width=True):
            # 转换为datetime对象
            start_datetime = datetime.datetime.combine(start_time, datetime.time.min)
            end_datetime = datetime.datetime.combine(end_time, datetime.time.max)

            with st.spinner("正在执行性能测试..."):
                results = run_single_station_test(station_id, start_datetime, end_datetime)
                if results:
                    st.session_state.single_station_results = results

    with col2:
        if hasattr(st.session_state, 'single_station_results') and st.session_state.single_station_results:
            results = st.session_state.single_station_results

            # 显示性能对比
            st.markdown('<div class="performance-card">', unsafe_allow_html=True)
            st.markdown(f"### ⚡ 性能测试结果 - 测站 {results['station_id']}")

            perf_col1, perf_col2, perf_col3 = st.columns(3)
            with perf_col1:
                st.metric(
                    "TimescaleDB 查询时间",
                    f"{results['timeseries_time']:.4f}s",
                    help="时序表查询执行时间"
                )
            with perf_col2:
                st.metric(
                    "PostgreSQL 查询时间",
                    f"{results['regular_time']:.4f}s",
                    help="普通表查询执行时间"
                )
            with perf_col3:
                if results['performance_ratio'] > 1:
                    improvement = f"{((results['performance_ratio'] - 1) * 100):.1f}%"
                    st.metric(
                        "性能提升",
                        improvement,
                        delta=improvement,
                        help="TimescaleDB相对于PostgreSQL的性能提升"
                    )
                else:
                    st.metric("性能提升", "0%", help="性能对比结果")
            st.markdown('</div>', unsafe_allow_html=True)

            # 性能对比图表
            perf_chart = create_performance_comparison_chart(
                results['timeseries_time'],
                results['regular_time'],
                "单测站查询"
            )
            if perf_chart:
                st.plotly_chart(perf_chart, use_container_width=True)

            # 数据可视化对比
            st.markdown('<h3 class="section-title">📈 数据可视化对比</h3>', unsafe_allow_html=True)

            # TimescaleDB数据图表
            if results['timeseries_data']:
                ts_chart = create_timeseries_chart(
                    results['timeseries_data'],
                    "TimescaleDB 多测站聚合数据",
                    results['station_id']
                )
                if ts_chart:
                    st.markdown('<div class="timeseries-card">', unsafe_allow_html=True)
                    st.plotly_chart(ts_chart, use_container_width=True)
                    st.markdown('</div>', unsafe_allow_html=True)
            else:
                st.info("TimescaleDB: 该时间范围内无数据")

            # PostgreSQL数据图表
            if results['regular_data']:
                reg_chart = create_timeseries_chart(
                    results['regular_data'],
                    "PostgreSQL 时序数据",
                    results['station_id']
                )
                if reg_chart:
                    st.markdown('<div class="regular-card">', unsafe_allow_html=True)
                    st.plotly_chart(reg_chart, use_container_width=True)
                    st.markdown('</div>', unsafe_allow_html=True)
            else:
                st.info("PostgreSQL: 该时间范围内无数据")

            # 数据统计对比
            with st.expander("📊 详细数据统计", expanded=False):
                stat_col1, stat_col2 = st.columns(2)
                with stat_col1:
                    st.markdown("**TimescaleDB 统计**")
                    if results['timeseries_data']:
                        ts_df = pd.DataFrame(results['timeseries_data'])
                        st.write(f"数据点数: {len(ts_df)}")
                        if 'water_level' in ts_df.columns:
                            st.write(f"平均水位: {ts_df['water_level'].mean():.2f}m")
                        if 'flow_rate' in ts_df.columns:
                            st.write(f"平均流量: {ts_df['flow_rate'].mean():.2f}m³/s")
                    else:
                        st.write("无数据")

                with stat_col2:
                    st.markdown("**PostgreSQL 统计**")
                    if results['regular_data']:
                        reg_df = pd.DataFrame(results['regular_data'])
                        st.write(f"数据点数: {len(reg_df)}")
                        if 'water_level' in reg_df.columns:
                            st.write(f"平均水位: {reg_df['water_level'].mean():.2f}m")
                        if 'flow_rate' in reg_df.columns:
                            st.write(f"平均流量: {reg_df['flow_rate'].mean():.2f}m³/s")
                    else:
                        st.write("无数据")
        else:
            st.info("👈 请在左侧配置测试参数并点击开始测试")


def display_multi_station_test():
    """显示多测站测试界面"""
    st.markdown('<h2 class="section-title">🏢 多测站聚合数据测试</h2>', unsafe_allow_html=True)

    col1, col2 = st.columns([1, 2])

    with col1:
        st.markdown(
            """
            ### 聚合测试参数\n
            测试多个测站的数据聚合查询性能
            """
        )

        # 获取可用测站
        available_stations = get_available_stations(50)
        if not available_stations:
            st.error("无法获取测站列表")
            return

        selected_stations = st.multiselect(
            "选择测站 (建议选择3-10个)",
            available_stations,
            default=available_stations[:5],
            help="选择要进行聚合查询的测站"
        )

        if not selected_stations:
            st.warning("请至少选择一个测站")
            return

        # 获取数据库时间范围
        min_time, max_time = get_database_time_range()
        if not min_time or not max_time:
            st.error("无法获取数据库时间范围")
            return

        st.info(f"数据库时间范围: {min_time.strftime('%Y-%m-%d %H:%M')} 至 {max_time.strftime('%Y-%m-%d %H:%M')}")

        col_time1, col_time2 = st.columns(2)
        with col_time1:
            # 默认开始时间为最近30天前
            default_start_time = max_time.date() - datetime.timedelta(days=30)
            if default_start_time < min_time.date():
                default_start_time = min_time.date()
            start_time = st.date_input(
                "开始时间",
                value=default_start_time,
                min_value=min_time.date(),
                max_value=max_time.date(),
                help="选择聚合查询开始时间",
                key="multi_start"
            )
        with col_time2:
            end_time = st.date_input(
                "结束时间",
                value=max_time.date(),
                min_value=min_time.date(),
                max_value=max_time.date(),
                help="选择聚合查询结束时间",
                key="multi_end"
            )

        # 验证时间范围
        if start_time >= end_time:
            st.error("开始时间必须早于结束时间")
            return

        if st.button("🚀 开始聚合测试", type="primary", use_container_width=True):
            # 转换为datetime对象
            start_datetime = datetime.datetime.combine(start_time, datetime.time.min)
            end_datetime = datetime.datetime.combine(end_time, datetime.time.max)

            with st.spinner("正在执行多测站聚合测试..."):
                results = run_multi_station_test(selected_stations, start_datetime, end_datetime)
                if results:
                    st.session_state.multi_station_results = results

    with col2:
        if hasattr(st.session_state, 'multi_station_results') and st.session_state.multi_station_results:
            results = st.session_state.multi_station_results

            # 显示性能对比
            st.markdown('<div class="performance-card">', unsafe_allow_html=True)
            st.markdown(f"### ⚡ 聚合查询性能结果 - {results['station_count']} 个测站")

            perf_col1, perf_col2, perf_col3 = st.columns(3)
            with perf_col1:
                st.metric(
                    "TimescaleDB 聚合时间",
                    f"{results['timeseries_time']:.4f}s",
                    help="时序表聚合查询执行时间"
                )
            with perf_col2:
                st.metric(
                    "PostgreSQL 聚合时间",
                    f"{results['regular_time']:.4f}s",
                    help="普通表聚合查询执行时间"
                )
            with perf_col3:
                if results['performance_ratio'] > 1:
                    improvement = f"{((results['performance_ratio'] - 1) * 100):.1f}%"
                    st.metric(
                        "性能提升",
                        improvement,
                        delta=improvement,
                        help="TimescaleDB相对于PostgreSQL的性能提升"
                    )
                else:
                    st.metric("性能提升", "0%", help="性能对比结果")
            st.markdown('</div>', unsafe_allow_html=True)

            # 性能对比图表
            perf_chart = create_performance_comparison_chart(
                results['timeseries_time'],
                results['regular_time'],
                "多测站聚合查询"
            )
            if perf_chart:
                st.plotly_chart(perf_chart, use_container_width=True)

            # 聚合数据可视化对比
            st.markdown('<h3 class="section-title">📊 聚合数据可视化对比</h3>', unsafe_allow_html=True)

            # TimescaleDB聚合数据图表
            if results['timeseries_agg']:
                ts_agg_chart = create_multi_station_chart(
                    results['timeseries_agg'],
                    "TimescaleDB 多测站聚合数据"
                )
                if ts_agg_chart:
                    st.markdown('<div class="timeseries-card">', unsafe_allow_html=True)
                    st.plotly_chart(ts_agg_chart, use_container_width=True)
                    st.markdown('</div>', unsafe_allow_html=True)
            else:
                st.info("TimescaleDB: 该时间范围内无聚合数据")

            # PostgreSQL聚合数据图表
            if results['regular_agg']:
                reg_agg_chart = create_multi_station_chart(
                    results['regular_agg'],
                    "PostgreSQL 多测站聚合数据"
                )
                if reg_agg_chart:
                    st.markdown('<div class="regular-card">', unsafe_allow_html=True)
                    st.plotly_chart(reg_agg_chart, use_container_width=True)
                    st.markdown('</div>', unsafe_allow_html=True)
            else:
                st.info("PostgreSQL: 该时间范围内无聚合数据")

            # 聚合数据详细对比
            with st.expander("📈 聚合数据详细对比", expanded=False):
                agg_col1, agg_col2 = st.columns(2)
                with agg_col1:
                    st.markdown("**TimescaleDB 聚合结果**")
                    if results['timeseries_agg']:
                        ts_agg_df = pd.DataFrame(results['timeseries_agg'])
                        st.dataframe(ts_agg_df, use_container_width=True)
                    else:
                        st.write("无聚合数据")

                with agg_col2:
                    st.markdown("**PostgreSQL 聚合结果**")
                    if results['regular_agg']:
                        reg_agg_df = pd.DataFrame(results['regular_agg'])
                        st.dataframe(reg_agg_df, use_container_width=True)
                    else:
                        st.write("无聚合数据")
        else:
            st.info("👈 请在左侧配置测试参数并点击开始聚合测试")


def render_timeseries_demo():
    """渲染时序数据库演示页面"""
    st.title("💧 HydroPulse-DX 时序数据库教学演示平台")

    # 初始化会话状态
    if 'single_station_results' not in st.session_state:
        st.session_state.single_station_results = None
    if 'multi_station_results' not in st.session_state:
        st.session_state.multi_station_results = None

    # 创建标签页
    tab1, tab2, tab3 = st.tabs(["📊 数据库概览", "🎯 单测站测试", "🏢 多测站测试"])

    with tab1:
        display_database_overview()

    with tab2:
        display_single_station_test()

    with tab3:
        display_multi_station_test()
